import pymysql
from datetime import datetime
from flask_cors import CORS

from flask import Flask, request, jsonify, render_template

# 数据库连接
# 记得调整数据库名字和账号密码
host = "127.0.0.1"
user = "root"
password = "rxh12321..."
datebase = "mogu"
db = pymysql.connect(host=host, user=user, password=password, database=datebase, charset="utf8")
cursor = db.cursor()

# 后端服务启动
app = Flask(__name__)
app.config['SECRET_KEY'] = 'secret!'

# 解决跨域问题
CORS(app, resources={r"/*": {"origins": "*"}})

# 轮询间距
global tnum
tnum = 10

# 轮询计数器
global tcount
tcount = {}
tcount["temp1"] = 1
tcount["temp2"] = 1
tcount["temp3"] = 1
tcount["humidity1"] = 1
tcount["humidity2"] = 1
tcount["humidity3"] = 1
tcount["beam"] = 1
tcount["CO2"] = 1
tcount["all"] = 1


# 直接进网页
@app.route('/', methods=['GET', 'POST'])
def init():
    if request.method == 'GET':
        return 'Connect Success // method : GET'
    elif request.method == 'POST':
        return 'Connect Success // method : POST'
    return 'Connect failed'


# 测试
@app.route('/test', methods=['POST'])
def test():
    result = {'key': 'value114514'}
    return jsonify(result)


# 获取所有数据列
@app.route('/res', methods=['POST'])
def getSome():
    if request.method == 'POST':
        db = pymysql.connect(host=host, user=user, password=password, database=datebase, charset="utf8")
        cursor = db.cursor()
        index = request.form.get('index')
        num = request.form.get('num')
        cursor.execute("SELECT time,temp1,temp2,temp3,"
                       "humidity1,humidity2,humidity3,beam,CO2,`index` FROM outdata WHERE `index` between "
                       + index + ' and '
                       + str(int(index) + int(num) - 1))
        data = cursor.fetchall()
        temp = {}
        result = []
        timeModule = '%Y-%m-%d %H:%M:%S'
        if data:
            for i in data:
                t = str(datetime.strptime(str(i[0]), '%Y-%m-%d %H:%M:%S').strftime(timeModule))
                temp["temp1"] = i[1]
                temp["temp2"] = i[2]
                temp["temp3"] = i[3]
                temp["humidity1"] = i[4]
                temp["humidity2"] = i[5]
                temp["humidity3"] = i[6]
                temp["beam"] = i[7]
                temp["CO2"] = i[8]
                temp["index"] = i[9]
                temp = {t: temp}
                result.append(temp.copy())
                temp.clear()
            db.close()
            return jsonify(result)
        else:
            db.close()
            return jsonify([])
    return jsonify({})


# 获取指定数据列
@app.route('/res/<type>', methods=['POST'])
def getResource(type):
    if request.method == 'POST':
        db = pymysql.connect(host=host, user=user, password=password, database=datebase, charset="utf8")
        cursor = db.cursor()
        result = {}
        index = request.form.get("index")
        num = request.form.get('num')
        timeModule = '%H:%M'
        for i in range(int(num)):
            cursor.execute("SELECT `time`," + type + " FROM outdata WHERE `index` =" + str(int(index) + int(i)))
            data = cursor.fetchone()
            if data:
                t = str(datetime.strptime(str(data[0]), '%Y-%m-%d %H:%M:%S').strftime(timeModule))
                result[str(t)] = data[1]
        db.close()
        return jsonify(result)
    else:
        return jsonify({})
    return jsonify({})


# 轮询指定数据列
@app.route('/turn/<type>', methods=['GET'])
def takeTurn(type):
    if request.method == 'GET':
        db = pymysql.connect(host=host, user=user, password=password, database=datebase, charset="utf8")
        cursor = db.cursor()
        result = {}
        global tcount
        global tnum
        timeModule = '%H:%M'
        for i in range(tnum):
            cursor.execute("SELECT `time`," + type + " FROM outdata WHERE `index` =" + str(tcount[type] + int(i)))
            data = cursor.fetchone()
            if data:
                t = str(datetime.strptime(str(data[0]), '%Y-%m-%d %H:%M:%S').strftime(timeModule))
                result[str(t)] = data[1]
        tcount[type] = tcount[type] + tnum
        db.close()
        return jsonify(result)
    else:
        return jsonify({})
    return jsonify({})

# 轮询获取所有数据列
@app.route('/turn', methods=['GET'])
def getAll():
    if request.method == 'GET':
        db = pymysql.connect(host=host, user=user, password=password, database=datebase, charset="utf8")
        cursor = db.cursor()
        global tcount
        global tnum
        end = tcount["all"] + tnum - 1
        cursor.execute("SELECT time,temp1,temp2,temp3,"
                       "humidity1,humidity2,humidity3,beam,CO2,`index` FROM outdata WHERE `index` between "
                       + str(tcount["all"]) + ' and '
                       + str(end))
        data = cursor.fetchall()
        temp = {}
        result = []
        timeModule = '%H:%M'
        if data:
            for i in data:
                t = str(datetime.strptime(str(i[0]), '%Y-%m-%d %H:%M:%S').strftime(timeModule))
                temp["temp1"] = i[1]
                temp["temp2"] = i[2]
                temp["temp3"] = i[3]
                temp["humidity1"] = i[4]
                temp["humidity2"] = i[5]
                temp["humidity3"] = i[6]
                temp["beam"] = i[7]
                temp["CO2"] = i[8]
                temp["index"] = i[9]
                temp = {t: temp}
                result.append(temp.copy())
                temp.clear()
            tcount["all"] = tcount["all"] + tnum
            db.close()
            return jsonify(result)
        else:
            db.close()
            return jsonify([])
    return jsonify({})



if __name__ == '__main__':
    app.run(debug=True, port=8080, host='0.0.0.0')  # 设置debug=True是为了让代码修改实时生效，而不用每次重启加载
    db.close()
